package com.raidan.dclog.core;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.Properties;

import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * DB support class. Now we are using DerbyDB. Later there will be no obstacles
 * to use any other DB (SQLite3, for example).
 * 
 * Mmmm, how good is return to coding ^_^
 * 
 * @author raidan
 * 
 */
public class Database {

	private Database() {
		//
	}

	private final static Logger logger = LoggerFactory.getLogger(Database.class);

	private static Database instance;

	/**
	 * Singleton - only one instance of this class available
	 * 
	 * @return instance
	 */
	public static synchronized Database getInstance() {
		if (instance == null) {
			logger.debug("Initializing Database instance.");
			instance = new Database();

			instance.loadScripts();
		}
		return instance;
	}

	public boolean hasConnection() {
		return this.connection != null;
	}

	private Connection connection;

	/**
	 * Return connection to database. Connection only one! There will be close
	 * by closeConnection method, if you want.
	 * 
	 * @return
	 * @throws Exception
	 */
	public Connection getConnection() throws SQLException {
		if (connection == null) {
			try {
				String configFile = Config.getInstance().getSqlConfigName();
				Configuration config;

				config = new PropertiesConfiguration(configFile);
				// ResourceBundle resource =
				// ResourceBundle.getBundle(configFile);

				String driver = config.getString("driver");
				String url = config.getString("url");

				if (driver == null || driver.trim().length() == 0) {
					throw new RuntimeException("Invalid config file '" + configFile + "'. 'driver' is empty.");
				}

				if (url == null || url.trim().length() == 0) {
					throw new RuntimeException("Invalid config file '" + configFile + "'. 'url' is empty.");
				}

				Properties properties = new Properties();
				properties.put("user", config.getString("user"));
				properties.put("password", config.getString("password"));

				// We can load additional options for database and put them into
				// System.properties
				// Yes, this is especially for Derby DB
				// Later we can change method for loading additional options
				// into properties of connection
				boolean foundOptions = false;
				Iterator iter = config.getKeys("options");
				while (iter.hasNext()) {
					foundOptions = true;
					String option = iter.next().toString();
					String value = config.getString(option);

					option = option.substring("options.".length());
					System.setProperty(option, value);
					logger.trace("Set additional option {}={}", option, value);
				}

				if (!foundOptions) {
					logger.trace("There is not additional options for config {}.", configFile);
				}

				logger.debug("Opening database instance ({}) as {}", driver, url);

				try {
					DriverManager.registerDriver((Driver) Class.forName(driver).newInstance());
				} catch (Exception e) {
					throw new RuntimeException("Unable to register driver " + driver, e);
				}

				connection = DriverManager.getConnection(url);

				// Special for init statements (PRAGMA in SQLite3)
				String inits[] = config.getStringArray("sql-init");
				if (inits != null) {
					Statement st = connection.createStatement();
					try {
						for (String init : inits) {
							logger.trace("Execute init statement {}", init);
							st.execute(init);
						}
					} finally {
						st.close();
					}
				}
				
				connection.setAutoCommit(false);

			} catch (ConfigurationException ce) {
				throw new RuntimeException(ce);
			}

		}
		return connection;
	}

	//
	// public Connection getAnotherConnection() throws SQLException {
	// if (this.connection == null) {
	// throw new
	// IllegalStateException("Unable to receive another connection, where main connection not received.");
	// }
	// Connection conn = null;
	//
	// conn = DriverManager.getConnection(PROTOCOL + databaseName);
	// conn.setAutoCommit(false);
	//
	// return conn;
	// }

	private final static String SCRIPTS_FILE = "com/raidan/dclog/core/prepareSQL.properties";
	private final static String DROP_TABLE_PREFIX = "drop table";
	private final static String CREATE_INDEX_PREFIX = "create index";
	private final static int VERSION = 5;

	/**
	 * Mehod for loading scripts for database
	 */
	private void loadScripts() {
		if (tableList != null) {
			return; //
		}

		tableList = new ArrayList<String>();
		scripts4drop = new ArrayList<String>();
		scripts4create = new ArrayList<String>();
		indexes = new ArrayList<String>();

		logger.debug("Loading script from '{}'", SCRIPTS_FILE);

		try {

			/*
			 * Yes, this is ugly code. We change them later.
			 */

			StringBuilder text = FileUtils.loadFromResource(SCRIPTS_FILE);
			String sqlPrepare[] = text.toString().split(";");
			for (String sql : sqlPrepare) { // #1

				// Remove all comments
				String rows[] = sql.split("\n");
				String resultSQL = "";
				for (String row : rows) {
					if (!row.startsWith("--")) {
						resultSQL += row + "\n";
					} else {
						logger.trace("Skipping comment line '{}'", row);
					}
				}

				resultSQL = resultSQL.trim();

				if (resultSQL.length() == 0/* || sql.startsWith("#") */) {
					continue; //
				}

				String lower = resultSQL.toLowerCase();
				if (lower.startsWith(DROP_TABLE_PREFIX)) {
					logger.trace("Append drop script '{}.'", resultSQL);
					tableList.add(resultSQL.substring(DROP_TABLE_PREFIX.length()));

					scripts4drop.add(resultSQL);
				} else if (lower.startsWith(CREATE_INDEX_PREFIX)) {
					logger.trace("Append create index script '{}.'", resultSQL);

					indexes.add(resultSQL);
				} else {
					logger.trace("Append create script '{}.'", resultSQL);

					scripts4create.add(resultSQL);
				}
			} // #1 if

		} catch (IOException ex) {
			throw new RuntimeException("Unable to find '" + SCRIPTS_FILE + "' resource.");
		}

	}

	/**
	 * Check for correct database
	 * 
	 * @return true if OK and false if database is empty or in incorrect version
	 * @throws SQLException
	 */
	public boolean isDatabaseExists() throws SQLException {
		Statement st = null;
		try {
			st = this.getConnection().createStatement();

			logger.debug("Checking current database data for correct previous loading.");

			for (String table : this.tableList) {
				st.execute("select count(*) from " + table + " where 1 = 0");
			}
			ResultSet rs = null;
			try {

				// Well, not good idea. But still useful
				rs = st.executeQuery("select all_ok, version from loadresult");
				rs.next();
				int all_ok_flag = rs.getInt(1);
				int version_flag = rs.getInt(2);
				if (all_ok_flag != 1 || version_flag != VERSION) {
					logger.trace("Database not correct. ALL_OK flag is " + all_ok_flag + " and VERSION flag is "
							+ version_flag + " (current version is " + VERSION + ").");
					return false; //
				}
				if (rs.next()) {
					logger.trace("Database not correct. More than one row in table 'loadresult'.");
					return false; //
				}

				logger.trace("Database is correct. Flag is 1 and one row present.");
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		} catch (SQLException sql) {
			logger.trace("Database is not correct.Exception thrown.", sql);
			return false; //
		} finally {
			if (st != null) {
				st.close();
			}
		}

		return true;
	}

	/**
	 * Simple table names
	 */
	private Collection<String> tableList = null;

	/**
	 * Scripts for drop tables
	 */
	private Collection<String> scripts4drop = null;

	/**
	 * Scripts for create tables
	 */
	private Collection<String> scripts4create = null;

	/**
	 * Scripts for indexes. All scripts for indexes will applied after loading
	 * data into tables.
	 */
	private Collection<String> indexes = null;

	/**
	 * Cleaning database
	 * 
	 * @throws SQLException
	 */
	public void clearDatabase() throws SQLException {

		logger.debug("Cleaning database and recreating.");

		// Drop then create

		Connection conn = this.getConnection();
		Statement st = conn.createStatement();
		try {

			for (String sql : scripts4drop) {
				try {
					logger.trace("Execuing sql for drop table: '" + sql + "'.");

					st.execute(sql);
				} catch (SQLException sqlEx) {
					logger.trace("During drop table we received exception. This is OK.", sqlEx);
					// do nothing
				}
			}

			for (String sql : scripts4create) {
				try {
					logger.trace("Execuing sql for create table: '" + sql + "'.");

					st.execute(sql);
				} catch (SQLException sqlEx) {
					throw new RuntimeException("Error during executing script: " + sql, sqlEx);
				}
			}

			logger.trace("Initiaing table 'loadresult'.");
			st.execute("insert into loadresult (version, all_ok) values (" + VERSION + ", 0)");

			conn.commit();
		} finally {
			st.close();
		}
	}

	/**
	 * Confirming that all data loaded successfully (called after parsing).
	 * Commit will be executed!
	 * 
	 * @throws SQLException
	 */
	public void confirmDatabaseOK() throws SQLException {

		logger.debug("Confirm, that database is OK.");

		Connection conn = this.getConnection();
		Statement st = conn.createStatement();

		try {
			st.execute("update loadresult set all_ok = 1");

			logger.info("Applying indexes to tables...");

			Config.getInstance().getProgressInstance().processStart(false);

			int i = 0;
			for (String sql : indexes) {
				Config.getInstance().getProgressInstance().process(i++, indexes.size());
				try {
					logger.trace("Execuing sql for create index: '" + sql + "'.");

					st.execute(sql);
				} catch (SQLException sqlEx) {
					throw new RuntimeException("Error during executing script: " + sql, sqlEx);
				}
			}
			conn.commit();
		} finally {
			Config.getInstance().getProgressInstance().processOver();
			st.close();
		}
	}

	/**
	 * Closing connection. This connection can be reopened -- just call
	 * getConnection again.
	 * 
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException {
		if (connection != null) {
			try {

				logger.debug("Closing existing connection.");

				connection.rollback();
				connection.close();
				DriverManager.getConnection("jdbc:derby:;shutdown=true");

			} catch (SQLException sql) {
				logger.trace("Exception during closing connection. This is OK.", sql);
				// do nothing
			} finally {
				connection = null;
			}
		}
	}
}
